package com.zyw.boot.tool;

import com.google.gson.JsonObject;
import com.zyw.boot.tool.util.HttpUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.net.URL;
import java.nio.charset.Charset;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.*;

public class ExcelTranslator2 {

    // 科大讯飞翻译API配置（保持不变）
    private static final String APP_ID = "0e511074";
    private static final String API_KEY = "ce04edfd2813fc4ab6c8b065050102eb";
    private static final String API_SECRET = "MGQ0NDNiMGJkNTA3MmVhYTEwYWVlOTUw";
    private static final String API_URL = "https://ntrans.xfyun.cn/v2/ots";

    // 文件路径配置
    private static final String INPUT_FOLDER = "D:\\share\\tanslator";
    private static final String OUTPUT_FOLDER = "D:\\share\\tanslator_chinese";
    private static final String LOG_FOLDER = "D://log";

    // Excel列配置
    private static final int COL_ID = 0;
    private static final int COL_NAME = 1;
    private static final int COL_COMMENTS = 2;
    private static final int COL_NAME_ZH = 3;
    private static final int COL_COMMENTS_ZH = 4;

    private static final int THREAD_COUNT = 100; // 可调节的并行度
    private static final int MAX_TEXT_LENGTH = 5000;

    private static final BlockingQueue<TranslationResult> resultQueue = new LinkedBlockingQueue<>();

    public static void main(String[] args) {
        createFolder(OUTPUT_FOLDER);
        createFolder(LOG_FOLDER);

        File folder = new File(INPUT_FOLDER);
        File[] files = folder.listFiles((dir, name) -> name.endsWith(".xlsx"));

        if (files == null || files.length == 0) {
            System.out.println("未找到Excel文件！");
            return;
        }

        ExecutorService executor = Executors.newFixedThreadPool(THREAD_COUNT);

        for (File file : files) {
            try {
                processExcelWithConcurrency(file, executor);
                System.out.println("文件 " + file.getName() + " 处理完成！");
            } catch (Exception e) {
                logError("处理文件 " + file.getName() + " 时出错: " + e.getMessage());
                e.printStackTrace();
            }
        }

        executor.shutdown();
    }

    private static void processExcelWithConcurrency(File inputFile, ExecutorService executor) throws Exception {
        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(inputFile))) {
            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) headerRow = sheet.createRow(0);

            createCell(headerRow, COL_NAME_ZH, "name_chinese");
            createCell(headerRow, COL_COMMENTS_ZH, "comments_chinese");

            // 第一阶段：收集所有翻译任务
            List<TranslationTask> allTasks = collectTranslationTasks(sheet);

            // 第二阶段：分块处理任务
            processTasksConcurrently(allTasks, executor);

            // 第三阶段：批量写入结果
            writeTranslationResults(sheet);

            // 保存文件
            File outputFile = new File(OUTPUT_FOLDER, inputFile.getName());
            try (FileOutputStream fos = new FileOutputStream(outputFile)) {
                workbook.write(fos);
            }
        }
    }

    // 收集需要翻译的任务（行号+内容）
    private static List<TranslationTask> collectTranslationTasks(Sheet sheet) {
        List<TranslationTask> tasks = new ArrayList<>();
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;

            tasks.add(new TranslationTask(rowNum, "name", getCellValue(row.getCell(1))));
            tasks.add(new TranslationTask(rowNum, "comment", getCellValue(row.getCell(2))));
        }
        return tasks;
    }

    // 多线程处理任务（核心改进点）
    private static void processTasksConcurrently(List<TranslationTask> tasks, ExecutorService executor)
            throws InterruptedException {

        // 将任务分块处理
        List<List<TranslationTask>> chunks = partitionTasks(tasks, THREAD_COUNT);

        // 使用CountDownLatch同步线程
        CountDownLatch latch = new CountDownLatch(chunks.size());

        for (List<TranslationTask> chunk : chunks) {
            executor.execute(() -> {
                try {
                    processTaskChunk(chunk);
                } finally {
                    latch.countDown();
                }
            });
        }

        // 等待所有任务完成
        latch.await();
    }

    // 处理单个任务块
    private static void processTaskChunk(List<TranslationTask> chunk) {
        for (TranslationTask task : chunk) {
            try {
                if (!task.sourceText.isEmpty()) {
                    String translated = translateText(task.sourceText);
                    resultQueue.put(new TranslationResult(
                            task.rowNum,
                            task.columnType,
                            translated
                    ));
                }
            } catch (Exception e) {
                logError(String.format("翻译失败 [行%d][%s]: %s",
                        task.rowNum, task.columnType, e.getMessage()));
            }
        }
    }

    // 批量写入结果到Excel
    private static void writeTranslationResults(Sheet sheet) {
        while (!resultQueue.isEmpty()) {
            TranslationResult result = resultQueue.poll();
            Row row = sheet.getRow(result.rowNum);
            if (row == null) continue;

            int targetCol = result.columnType.equals("name") ?
                    COL_NAME_ZH : COL_COMMENTS_ZH;
            createCell(row, targetCol, result.translatedText);
        }
    }

    // 辅助方法：任务分块
    private static List<List<TranslationTask>> partitionTasks(List<TranslationTask> tasks, int chunks) {
        List<List<TranslationTask>> partitions = new ArrayList<>();
        int size = tasks.size();
        int perChunk = (size + chunks - 1) / chunks;

        for (int i = 0; i < size; i += perChunk) {
            int end = Math.min(i + perChunk, size);
            partitions.add(tasks.subList(i, end));
        }
        return partitions;
    }

    // 翻译方法（优化版，添加流量控制）
    private static String translateText(String text) throws Exception {
        if (text.length() > MAX_TEXT_LENGTH) {
            return translateLargeText(text);
        }

        // 原有翻译逻辑（略，保持与之前相同）
        // 添加请求间隔控制
        Thread.sleep(100);
        return doTranslate(text);
    }

    // 处理长文本拆分
    private static String translateLargeText(String text) throws Exception {
        StringBuilder result = new StringBuilder();
        for (String chunk : splitText(text, MAX_TEXT_LENGTH)) {
            result.append(doTranslate(chunk));
            Thread.sleep(150); // 降低长文本请求频率
        }
        return result.toString();
    }

    // 文本分块逻辑
    private static List<String> splitText(String text, int chunkSize) {
        List<String> chunks = new ArrayList<>();
        for (int i = 0; i < text.length(); i += chunkSize) {
            chunks.add(text.substring(i, Math.min(i + chunkSize, text.length())));
        }
        return chunks;
    }

    // 数据载体类
    private static class TranslationTask {
        final int rowNum;
        final String columnType;
        final String sourceText;

        TranslationTask(int rowNum, String columnType, String sourceText) {
            this.rowNum = rowNum;
            this.columnType = columnType;
            this.sourceText = sourceText;
        }
    }

    private static class TranslationResult {
        final int rowNum;
        final String columnType;
        final String translatedText;

        TranslationResult(int rowNum, String columnType, String translatedText) {
            this.rowNum = rowNum;
            this.columnType = columnType;
            this.translatedText = translatedText;
        }
    }

    // 以下方法保持原有实现不变
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            default:
                return "";
        }
    }
    private static void createCell(Row row, int column, String value) {
        Cell cell = row.getCell(column);
        if (cell == null) {
            cell = row.createCell(column);
        }
        cell.setCellValue(value);
    }
    private static String doTranslate(String text) throws Exception {
        if (text == null || text.isEmpty()) return "";

        // 科大讯飞API调用（与原代码基本相同）
        SimpleDateFormat format = new SimpleDateFormat("EEE, dd MMM yyyy HH:mm:ss z", Locale.US);
        format.setTimeZone(TimeZone.getTimeZone("GMT"));
        String date = format.format(new Date());

        String body = buildHttpBody(text);
        String digestBase64 = "SHA-256=" + signBody(body);

        StringBuilder builder = new StringBuilder("host: ").append(new URL(API_URL).getHost()).append("\n")
                .append("date: ").append(date).append("\n")
                .append("POST ").append(new URL(API_URL).getPath()).append(" HTTP/1.1").append("\n")
                .append("digest: ").append(digestBase64);
        String sha = hmacsign(builder.toString(), API_SECRET);

        String authorization = String.format("api_key=\"%s\", algorithm=\"%s\", headers=\"%s\", signature=\"%s\"",
                API_KEY, "hmac-sha256", "host date request-line digest", sha);

        Map<String, String> header = new HashMap<>();
        header.put("Authorization", authorization);
        header.put("Content-Type", "application/json");
        header.put("Accept", "application/json,version=1.0");
        header.put("Host", new URL(API_URL).getHost());
        header.put("Date", date);
        header.put("Digest", digestBase64);

        Map<String, Object> resultMap = HttpUtil.doPost2(API_URL, header, body);
        if (resultMap != null && resultMap.containsKey("body")) {
            return parseTranslationResult(resultMap.get("body").toString());
        }
        return "翻译失败";
    }
    // 解析翻译结果
    private static String parseTranslationResult(String resultStr) {
        com.google.gson.JsonParser parser = new com.google.gson.JsonParser();
        com.google.gson.JsonObject resultJson = parser.parse(resultStr).getAsJsonObject();
        if (resultJson.has("data") && resultJson.get("data").isJsonObject()) {
            String dst = resultJson.getAsJsonObject("data").getAsJsonObject("result").getAsJsonObject("trans_result").get("dst").getAsString();

            return dst;
        }
        return "翻译失败";
    }
    private static String buildHttpBody(String text) throws Exception {
        JsonObject body = new JsonObject();
        JsonObject common = new JsonObject();
        JsonObject business = new JsonObject();
        JsonObject data = new JsonObject();

        // 填充common
        common.addProperty("app_id", APP_ID);

        // 填充business
        business.addProperty("from", "en"); // 源语言（假设为英文）
        business.addProperty("to", "zh"); // 目标语言

        // 填充data
        byte[] textByte = text.getBytes("UTF-8");
        String textBase64 = new String(Base64.getEncoder().encodeToString(textByte));
        data.addProperty("text", textBase64);

        // 填充body
        body.add("common", common);
        body.add("business", business);
        body.add("data", data);

        return body.toString();
    }

    // 对body进行SHA-256加密
    private static String signBody(String body) throws Exception {
        MessageDigest messageDigest;
        String encodestr = "";
        try {
            messageDigest = MessageDigest.getInstance("SHA-256");
            messageDigest.update(body.getBytes("UTF-8"));
            encodestr = new String(Base64.getEncoder().encodeToString(messageDigest.digest()));
        } catch (NoSuchAlgorithmException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return encodestr;
    }

    // hmacsha256加密
    private static String hmacsign(String signature, String apiSecret) throws Exception {
        Charset charset = Charset.forName("UTF-8");
        javax.crypto.Mac mac = javax.crypto.Mac.getInstance("hmacsha256");
        javax.crypto.spec.SecretKeySpec spec = new javax.crypto.spec.SecretKeySpec(apiSecret.getBytes(charset), "hmacsha256");
        mac.init(spec);
        byte[] hexDigits = mac.doFinal(signature.getBytes(charset));
        return new String(Base64.getEncoder().encodeToString(hexDigits));
    }





    // 记录错误日志
    private static void logError(String message) {
        try {
            File logFile = new File(LOG_FOLDER, "translation_errors.log");
            try (PrintWriter writer = new PrintWriter(new FileOutputStream(logFile, true))) {
                writer.println(new Date() + " - " + message);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 创建文件夹
    private static void createFolder(String folderPath) {
        File folder = new File(folderPath);
        if (!folder.exists()) {
            folder.mkdirs();
        }
    }




}